package cn.iune.backcenter.author.sysinfo;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.NoResultException;
import javax.persistence.Query;

import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;
import org.springframework.stereotype.Repository;

@Repository
public class SysInfoDaoJPAImpl extends BaseJPAImpl<SysInfo> implements ISysInfoDao {


	private String kSql;
	
//	@PersistenceContext 
//	private EntityManager em ;

	@Override
	public void insert(SysInfo obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(SysInfo.class, fID));
	}

	@Override
	public SysInfo update(SysInfo obj) {
		SysInfo objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public SysInfo getByID(String fID) {
		SysInfo singleResult = em.find(SysInfo.class, fID);
		return singleResult;
	}

	@Override
	public List<SysInfo> getAll() {
		kSql="SELECT SysInfo FROM SysInfo SysInfo ";
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}
	
	@Override
	public List<SysInfo> getAppList() {
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE SysInfo.systemKind='app' "
				+ " ORDER BY SysInfo.appId ASC, SysInfo.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}
	
	@Override
	public List<SysInfo> getAppList(String appId) {
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE SysInfo.systemKind='app' "
				+ " AND SysInfo.appId=:appId "
				+ " ORDER BY SysInfo.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("appId", appId);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}

	//获取功能树种的全部叶子列表（叶子才是具体的功能映射）
	@Override
	public List<SysInfo> getPagList(String appId) {
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE SysInfo.systemKind='pag' "
				+ " AND SysInfo.appId=:appId "
				+ " ORDER BY SysInfo.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("appId", appId);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}

	//获取功能树种的全部叶子列表（叶子才是具体的功能映射）
	@Override
	public List<SysInfo> getPagList() {
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE SysInfo.systemKind='pag' "
				+ " ORDER BY SysInfo.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}
	
	@Override
	public List<SysInfo> getRoots(String appId){
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE SysInfo.systemKind='app' "
//				+ " WHERE ( SysInfo.fParent is null ) "//这两个条件都可以
				+ " AND SysInfo.appId=:appId "
				+ " ORDER BY SysInfo.sortNo ASC "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("appId", appId);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
		
	}
	
	@Override
	public int getRootCount(String appId) {
		// TODO Auto-generated method stub
		kSql="SELECT COUNT(SysInfo) as count " 
				+ " FROM SysInfo SysInfo " 
				+ " WHERE ( SysInfo.fParent is null ) "
				+ " AND SysInfo.appId=:appId "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("appId", appId);
		
		long singleResult =  (long) query.getSingleResult();
		return (int) singleResult;
	}
	
	@Override
	public List<SysInfo> getChilds(String fParent) {
		// TODO Auto-generated method stub
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE ( SysInfo.fParent = :fParent ) "
				+ " ORDER BY SysInfo.sortNo ASC"
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}

	
	@Override
	public SysInfo getByNo(String systemCode) {
		//1、定义sql语句
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
				+ " WHERE ( SysInfo.systemCode = :systemCode ) "
//				+ " AND ( SysInfo.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("systemCode", systemCode);
//		query.setParameter("enterpriseNo", enterpriseNo);
		SysInfo singleResult = null;
		try{
			singleResult = (SysInfo) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
		}
		return singleResult;
	}



	/*
	public JSONObject query(JSONObject data) throws Exception {
		
		// 获取参数
		Object columns = data.getJSONObject("columns"); // 列定义
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		
		
		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		if (!Util.isEmptyString(search)) {
			// 增加过滤条件
			filters.add("unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		//处理过滤条件
		if (!Util.isEmptyString(filter)) {
			filters.add(filter);
		}

		
		// 按用户ID过滤，用于主从数据示例
		
//		String userID = data.getString("userID");
//		if (!Util.isEmptyString(userID)) {
//			filters.add("fUserID = ?");
//			sqlParams.add(userID);
//		}
		
		
		Table table = null;
		// 获取数据源连接
		Connection conn = Util.getConnection(DATASOURCE_U2);
		try {
			// 执行单表数据查询，返回Table
			table = Util.queryData(conn, TABLE_U2_FILEUNIT, columns, filters, "cDatetime DESC", sqlParams, offset, limit);
		} finally {
			// 必须关闭数据源连接
			conn.close();
		}
		
		JSONObject json = Transform.tableToJson(table);

		return json;
	}

	public int save(JSONObject object) throws Exception {
		// TODO Auto-generated method stub

		log.info("@RequestParam------"+object.toJSONString());
		// JSON转换Table
		Table table = Transform.jsonToTable(object);

		// 获取数据源连接
		Connection conn = Util.getConnection(DATASOURCE_U2);
		try {
			// 开启事务
			conn.setAutoCommit(false);
			try {
				// 保存Table
				Util.saveData(conn, table, TABLE_U2_FILEUNIT);
				// 提交事务
				conn.commit();
			} catch (SQLException e) {
				// 如果发生异常，首先回滚事务，然后把异常继续抛出
				conn.rollback();
				throw e;
			}
		} finally {
			// 必须关闭数据源连接
			conn.close();
		}

		return 0;
	}
	*/
	/*
	@Override
	public int save(JSONObject object) throws Throwable {
		// TODO Auto-generated method stub
		// JSON转换Table
		Table table = Transform.jsonToTable(object);
		List<Row> list = table.getRows();
		for(Row row : list){
			JSONObject javaObject= Transform.rowToJavaObject(table, row);
			SysInfo obj = JSON.toJavaObject(javaObject, SysInfo.class);
			if(RowState.NEW.equals(obj.getRowState())){
				this.insert(obj);
			}
			if(RowState.EDIT.equals(obj.getRowState())){
				this.update(obj);
			}
			if(RowState.DELETE.equals(obj.getRowState())){
				this.delete(obj.getfID());
			}
		}
		return 0;
	}
	*/
	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(SysInfo.class);
//		Class cls;
//		try {
//			cls = Class.forName("SysInfo");
//		} catch (ClassNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		String defColstr = Utils.getDefCols(cls);
		data.put("defColstr", defColstr);
		return Utils.newData(data);
	}
	
	@Override
	public JSONObject query(JSONObject data) throws SQLException {
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");

		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SysInfo.*";
		String from = "SysInfo SysInfo";
		
		String where = "";

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("systemCode LIKE ? OR systemName LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 2; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("SysInfo.fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("SysInfo.fParent='"+fParent+"'");
		}
	
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		
		//System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		
		return json;
	}
	
	/*
	@Override
	public JSONObject queryTreeByOrgID(JSONObject data) throws Throwable{
		
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");

		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		String select = "SysInfo.*"
				;
//		String from = "SysInfo SysInfo, SysInfoOrg SysInfoOrg";
		String from = "SysInfo SysInfo LEFT JOIN SysInfoOrg SysInfoOrg ON (LOCATE(SysInfoOrg.systemID,SysInfo.systemFullID)<>0)";
//		String from = "SysInfo SysInfo LEFT JOIN SysInfoOrg SysInfoOrg ON (SysInfoOrg.systemID=SysInfo.fID)";
		
		String where = "";
//		String where = "WHERE (SysInfo.systemID=SysInfo.fID)";
		//LOCATE(string1, string 2, [start])
//		String where = "WHERE (LOCATE(SysInfoOrg.systemID,SysInfo.systemFullID)<>0)";

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件
			filters.add("enterpriseNo LIKE ? OR enterpriseName LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 2; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("fParent='"+fParent+"'");
		}
	
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		System.out.println("sql-->"+sql);
		Connection conn = DataUtils.getConnection(em);
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		
		return json;
	}
	 */

	@Override
	public List<SysInfo> getChildren(String fParent) {
		// TODO Auto-generated method stub
		kSql="SELECT SysInfo FROM SysInfo SysInfo "
//				+ " WHERE ( SysInfo.systemFullID like '%:fParent%' ) "
				+ " WHERE ( SysInfo.systemFullID like '%" + fParent + "%' ) "
				+ " AND ( SysInfo.fID <> '" + fParent + "' ) "
				+ " ORDER BY SysInfo.sortNo DESC"
				;
		Query query = em.createQuery(kSql);
//		query.setParameter("fParent", fParent);
		@SuppressWarnings("unchecked")
		List<SysInfo> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public int getChildCount(String fParent) {
		// TODO Auto-generated method stub
		kSql= "SELECT COUNT(SysInfo) as count " 
				+ " FROM SysInfo SysInfo " 
				+ " WHERE ( SysInfo.fParent = :fParent ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("fParent", fParent);
		
		long singleResult =  (long) query.getSingleResult();
		return (int) singleResult;
	}

	//定制查询（查询出本表的全部数据再加上联合表的对应的数据）
	//列出已经审核上线的系统
	@Override
	public JSONObject getAppListAddOrg(String appId, String orgID) throws SQLException {
		
		// 获取参数
		JSONObject columns = null;//data.getJSONObject("columns"); // 列定义
		String orderBy = " SysInfo.sortNo ASC ";//data.getString("orderBy"); // 排序
		Integer limit = -1;//data.getInteger("limit"); // 分页查询的行数
		Integer offset = 0;//data.getInteger("offset"); // 分页查询的行偏移
		
		String format = "SELECT %s FROM %s %s %s ";
		String select = "SysInfo.*"
				+ ",SysInfoOrg.orgID"
				+ ",if(SysInfoOrg.orgID is null, 0, 1) AS sign "
				+ ",SysInfoOrg.enableFlag"
				;
		
//		String from = "SysInfo SysInfo";
		String from = "SysInfo SysInfo"
				+ " LEFT JOIN SysInfoOrg SysInfoOrg ON (SysInfo.fID=SysInfoOrg.systemID AND (SysInfoOrg.orgID='" + orgID + "' OR SysInfoOrg.orgID is null) ) "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		
		String where = " WHERE SysInfo.systemKind='app' AND SysInfo.appId='" + appId + "'";
		
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
//		System.out.println(sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		//System.out.println(json.toJSONString());
		return json;
	}

	//获取树形菜单（只是去除了没有开通的应用），未处理不显示和无权限的菜单
	@Override
	public JSONObject getTreeMenu(String appId, String orgID) throws SQLException  {
		
		// 获取参数
		JSONObject columns = null;//data.getJSONObject("columns"); // 列定义
		String orderBy = " SysInfo.sortNo ASC ";//data.getString("orderBy"); // 排序
		Integer limit = -1;//data.getInteger("limit"); // 分页查询的行数
		Integer offset = 0;//data.getInteger("offset"); // 分页查询的行偏移
		
		String format = "SELECT %s FROM %s %s %s ";
		String select = "SysInfo.*"
//				+ ",SysInfoOrg.orgID"
//				+ ",if(SysInfoOrg.orgID is null, 0, 1) AS sign "
//				+ ",SysInfoOrg.enableFlag"
				;
		
//		String from = "SysInfo SysInfo";
		String from = "SysInfo SysInfo"
//				+ " LEFT JOIN SysInfoOrg SysInfoOrg ON (SysInfoOrg.systemID IN SysInfo.systemFullID AND (SysInfoOrg.orgID='" + orgID + "' OR SysInfoOrg.orgID is null) ) "
				+ " LEFT JOIN SysInfoOrg SysInfoOrg ON (locate(SysInfoOrg.systemID , SysInfo.systemFullID)>0 AND SysInfoOrg.orgID='" + orgID + "' ) "
				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		
		String where = " WHERE SysInfo.appId='" + appId + "'" +" AND SysInfoOrg.enableFlag=1";
		
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
//		System.out.println(sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);
		//关闭连接
		conn.close();

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		//System.out.println(json.toJSONString());
		return json;
		
	}

		
}
